#!/usr/bin/python
# -*- coding:utf-8 -*-
# edit 2014/11/03
'''
' 生成SQL导入文本
' 通过校对员帐号进行校对
' 通过管理员帐号进行校对查看
' 放弃表示不通过，有效表示通过
'''
import time
import httplib
httplib.HTTPConnection._http_vsn = 10 
httplib.HTTPConnection._http_vsn_str = 'HTTP/1.0'
import socket
import MySQLdb as mdb
import random
from decimal import *
import os
socket.setdefaulttimeout(300)  # 设置全局超时60s

end_time = str(time.time())  # 发当前天之前的货
print end_time
shipping_status = "5"  # 0未发货 1已发货 2已到货

# 包装重量
p5 = 0.5
p10 = 1.1
p15 = 1.2
p20 = 1.2

# MYSQL
host = '47.88.0.143'
user = 'root'
passwd = 'ROOT-PASSWORD'
db = 'greenmyday'

'''
' 连接数据库导出 搜索结果
'''
def get_sql_result(sql):
    # print values
    
    
    # print sql
    con = mdb.connect(host, user, passwd, db, charset='utf8')
    cur = con.cursor() 
    
    count = cur.execute(sql)
    results = cur.fetchmany(count)  
    con.commit()
    cur.close()
    con.close()
    return results

import codecs
import traceback
import xml.dom.minidom as minidom
def covert_to_unicode(msg):
    '''将转入的编码转换为unicode，只接受ISO-8859-1和unicode编码'''
    __re_str = None
    if isinstance(msg, unicode):
        __re_str = msg
    elif isinstance(msg, str):
        try:
            __re_str = msg.decode('ISO-8859-1')
        except Exception, errinfo:
            raise Exception, '%s,%s' % (errinfo, str(msg))
    else:
        raise Exception, '%s 必须为str或unicode类型' % msg
    return __re_str
    
class CreateCovXml():
    def __init__(self, cov_xml_path):
        self.__cov_path = cov_xml_path
        self.__dom = None
        self.__root = None
        self.__shipment = None
        self.__receiver = None
        self.__cn23 = None
    
    def _covert_code(self, msg):
        return covert_to_unicode(msg)
    
    def __create_new_node(self, node_name, node_text=None):
        '''为xml文件添加一个结点,其中node_text可以为空'''        
        if self.__dom == None:
            raise Exception, '创建结点时，document对象还不存在'
        # 创建的是结点只有结点名
        if None == node_text:
            return self.__dom.createElement(self._covert_code(node_name))
        else:
            newNode = self.__dom.createElement(self._covert_code(node_name))
            newText = self.__dom.createTextNode(self._covert_code(node_text))
            newNode.appendChild(newText)
            return newNode
            
    def begin_cov(self, orderno):
        '''begin_cov(self) --> None 创建coverage.xml文档和根结点'''
        try:
            impl = minidom.getDOMImplementation()
            self.__dom = impl.createDocument(None, u'pacsoft', None)
            self.__root = self.__dom.documentElement
            new_receiver_node = self.__create_new_node("receiver")
            new_receiver_node.setAttribute('rcvid', 'SE')  # 这里填接收人的编号，这里没有
            new_shipment_node = self.__create_new_node("shipment")
            new_shipment_node.setAttribute('orderno', orderno)
            self.__receiver = new_receiver_node
            self.__shipment = new_shipment_node
            self.__cn23 = self.__create_new_node("cn23")
            self.__shipment.appendChild(self.__cn23)
            self.__root.appendChild(new_receiver_node)
            self.__root.appendChild(new_shipment_node)
        except:
            traceback.print_exc()
            raise Exception, '创建coverage xml根结点失败'
        
    def add_goods(self, goods, xishu):
        new_line_node = self.__create_new_node("line")
        new_line_node.setAttribute('measure', 'totals')
        new_statno_node = self.__create_new_node("val", str(goods[5]))  # 海关号
        new_statno_node.setAttribute('n', 'statno')
        new_quantity_node = self.__create_new_node("val", str(goods[3]))
        new_quantity_node.setAttribute('n', 'quantity')
        new_netweight_node = self.__create_new_node("val", str(goods[2] * goods[3]))
        new_netweight_node.setAttribute('n', 'netweight')
        new_customsvalue_node = self.__create_new_node("val", str((goods[4] * goods[3] * xishu).quantize(Decimal('0.01'))))
        new_customsvalue_node.setAttribute('n', 'customsvalue')
        new_contents_node = self.__create_new_node("val", goods[1])
        new_contents_node.setAttribute('n', 'contents')
        new_line_node.appendChild(new_statno_node)
        new_line_node.appendChild(new_quantity_node)
        new_line_node.appendChild(new_netweight_node)
        new_line_node.appendChild(new_customsvalue_node)
        new_line_node.appendChild(new_contents_node)
        self.__cn23.appendChild(new_line_node)

    def add_weight(self, weight):
        if weight + p5 < 5:
            weight = 5
        else:
            if weight + p10 < 10:
                weight = 10
            else:
                if weight + p15 < 15:
                    weight = 15
                else:
                    if weight + p20 < 20:
                        weight = 20
                    else:
                        weight = 20
                
        new_container_node = self.__create_new_node("container")
        new_container_node.setAttribute("type", "parcel")
        new_weight_node = self.__create_new_node("val", str(weight))
        new_weight_node.setAttribute("n", "weight")
        new_container_node.appendChild(new_weight_node)
        self.__shipment.appendChild(new_container_node)

    
    def creat_shipment(self,reference_value):
        new_from_node = self.__create_new_node("val", "1")
        new_from_node.setAttribute("n", "from")
        new_to_node = self.__create_new_node("val", "SE")  # Default
        new_to_node.setAttribute("n", "to")
        new_reference_node = self.__create_new_node("val", reference_value)  # Default
        new_reference_node.setAttribute("n", "reference")
        new_termcode_node = self.__create_new_node("val")
        new_termcode_node.setAttribute("n", "termcode")
        new_service_node = self.__create_new_node("service")
        new_service_node.setAttribute("srvid", "PDK340")  # Default
        new_addon_node = self.__create_new_node("addon")
        new_addon_node.setAttribute("adnid", "NOTEMAIL")
        new_service_node.appendChild(new_addon_node)

        # ufonline
        new_ufonline_node = self.__create_new_node("ufonline")
        new_option_node = self.__create_new_node("option")
        new_option_node.setAttribute('optid', "LNKPRTN")
        line_from_email_node = self.__create_new_node("val", "info@greenmyday.dk")
        line_from_email_node.setAttribute('n', 'from')
        line_to_email_node = self.__create_new_node("val", "guomodi@greenmyday.dk")
        line_to_email_node.setAttribute('n', 'to')
        new_option_node.appendChild(line_from_email_node)
        new_option_node.appendChild(line_to_email_node)
        new_ufonline_node.appendChild(new_option_node)
        
        self.__shipment.appendChild(new_from_node)
        self.__shipment.appendChild(new_to_node)
        self.__shipment.appendChild(new_reference_node)
        self.__shipment.appendChild(new_termcode_node)
        self.__shipment.appendChild(new_service_node)
        
        self.__shipment.appendChild(new_ufonline_node)
    def create_receiver(self, receiver):
        # 0 id 1 consignee 2 address 3 district 4 city 5 province 6 coutry 7 zipcode 8 tel 9 mobile 10 email 11 shipping_name 12 postscript
        line_name_node = self.__create_new_node("val", receiver[1])
        line_name_node.setAttribute('n', 'name')
        line_address1_node = self.__create_new_node("val", ",".join(receiver[2:6]))
        line_address1_node.setAttribute('n', 'address1')
        line_zipcode_node = self.__create_new_node("val", receiver[7])
        line_zipcode_node.setAttribute('n', 'zipcode')
        line_city_node = self.__create_new_node("val", receiver[3])
        line_city_node.setAttribute('n', 'city')
        line_country_node = self.__create_new_node("val", "CN")  # receiver[6]
        line_country_node.setAttribute('n', 'country')
        phone = receiver[8]
        if phone == '':
            phone = receiver[9]
        line_phone_node = self.__create_new_node("val", phone)
        line_phone_node.setAttribute('n', 'phone')
        line_contact_node = self.__create_new_node("val", receiver[1])
        line_contact_node.setAttribute('n', 'contact')
        new_receiver_node = self.__receiver
        
        new_receiver_node.appendChild(line_name_node)
        new_receiver_node.appendChild(line_address1_node)
        new_receiver_node.appendChild(line_zipcode_node)
        new_receiver_node.appendChild(line_city_node)
        new_receiver_node.appendChild(line_country_node)
        new_receiver_node.appendChild(line_phone_node)
        new_receiver_node.appendChild(line_contact_node)

    def end_cov(self):
        try:
            f = open(self.__cov_path, 'wb')
            writer = codecs.lookup('utf-8')[3](f)
            self.__dom.writexml(writer, encoding='utf-8')  
            writer.close()
            f.close()
            return True
        except Exception, err:
            traceback.print_exc() 
            raise Exception, '写coverage.xml文件出错'
            
if __name__ == "__main__":
    sql_addr_select = " SELECT info.order_id AS 'id', consignee,address,dis.region_name AS 'district',cit.region_name AS 'city',pro.region_name AS 'province',cot.region_name AS 'coutry',zipcode,tel,mobile,email,shipping_name,postscript,order_sn "
    # 0 id 1 consignee 2 address 3 district 4 city 5 province 6 coutry 7 zipcode 8 tel 9 mobile 10 email 11 shipping_name 12 postscript 13 order_sn
    sql_addr_from = "FROM ecs_order_info AS info,ecs_region AS cot,ecs_region AS pro,ecs_region AS cit,ecs_region AS dis "
    sql_addr_where = "WHERE info.add_time < " + end_time + " AND info.pay_status = 2 AND info.shipping_status = " + shipping_status + " AND (cot.region_id = info.country AND cot.region_type = 0 ) AND (pro.region_type = 1 AND pro.region_id = info.province ) AND (cit.region_type = 2 AND cit.region_id = info.city ) AND (dis.region_type = 3 AND dis.region_id = info.district )"
    sql_addr = sql_addr_select + sql_addr_from + sql_addr_where
    addr_values = get_sql_result(sql_addr)
    print '总计', str(len(addr_values)), '个订单需要处理...'
    sql_goods_select = "SELECT info.order_id AS 'id',ogoods.goods_name AS 'name',goods.goods_weight AS 'weight',ogoods.goods_number AS 'number', ogoods.goods_price AS 'price',goods.hs_code AS 'code' "
    sql_goods_from = "FROM ecs_order_info AS info,ecs_goods AS goods,ecs_order_goods AS ogoods "
    sql_goods_where = "WHERE info.order_id = ogoods.order_id AND goods.goods_id = ogoods.goods_id AND info.add_time < " + end_time + " AND info.pay_status = 2 AND info.shipping_status = " + shipping_status + " ORDER BY id"
    sql_goods = sql_goods_select + sql_goods_from + sql_goods_where
    goods_values = get_sql_result(sql_goods)
    print '总计', str(len(goods_values)), '个商品需要处理...'
    for value in addr_values:
        # print ".".join(value[1:]).encode("utf-8")
        goods = []
        weight = 0.0
        price = 0.0
        for goods_value in goods_values:
            if value[0] == goods_value[0]:
                goods.append(goods_value)
                price = price + float(goods_value[4] * goods_value[3])  # 统计价格
                weight = weight + float(goods_value[2] * goods_value[3])  # 统计重量
                # print goods_value
            if value[0] < goods_value[0]:
                break
        # 判断价格重量；
        xishu = 1
        if weight + p20 < 20 :
            print ' 订单： ', str(value[13]),' 货品重：',str(weight),'kg'
            if price > 1000:
                print ' 订单超过1000额度，赞不操作'  # xishu = Decimal(str(random.randint(900, 1000) / price))
        else:
            print ' 订单： ', str(value[13]), ' 超重！'
            # print str(xishu)
        
        if(len(goods) > 0):  # 有货则生成保单
            path = str(time.strftime("%Y%m%d")) + "/"
            xmlfile = CreateCovXml(path + str(value[13]) + ".xml")  # 创建xml
            if not os.path.exists(path):  # 若没有路径，则新建路径
                try:
                    os.makedirs(path)
                except Exception, s:
                    print 'new dir wrong', s, '\n', path
            xmlfile.begin_cov(str(value[13]))
            xmlfile.creat_shipment(value[1])#传入reference的值
            xmlfile.add_weight(weight)
            for good in goods:
                xmlfile.add_goods(good, xishu)
            xmlfile.create_receiver(value)
            xmlfile.end_cov()
    print '处理完成...'

